﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Runtime.Serialization.Json;
using System.IO;
using System.Xml;

namespace JqGrid
{
    public static class JqgridHelper
    {
        public enum DataType { String, Date, Number, Bool }

        public static DataType GetDataType(string data)
        {
            DateTime date;
            Double number;
            bool boolean;

            if (DateTime.TryParse(data, out date))
                return DataType.Date;
            else if (Double.TryParse(data, out number))
                return DataType.Number;
            else if (Boolean.TryParse(data, out boolean))
                return DataType.Bool;
            else
                return DataType.String;
        }

    }

    /// <summary>
    /// Takes the json string filter string sent from the jqgrid parses it and creates rules from it
    /// </summary>
    public class Filter
    {

        public string groupOp { get; set; }

        public Rule[] rules { get; set; }

        public static Filter Create(string jsonData)
        {
            try
            {
                var serializer = new DataContractJsonSerializer(typeof(Filter));
                MemoryStream ms = new MemoryStream(Encoding.Default.GetBytes(jsonData));
                return serializer.ReadObject(ms) as Filter;
            }
            catch
            {
                return null;
            }
        }

       

    }

    public class Rule
    {
       
        public string field { get; set; }

        public string op { get; set; }

        public string data { get; set; }

        public string expression(ExpressionParserFactory.ExpressionParserType ept)
        {
            ExpressionParser parser = ExpressionParserFactory.GetParser(ept);
            return parser.Parse(data, field, op);       
        }

        /// <summary>
        /// for getting a generic query string not intended to be suitable for any particular data set, I used it for debugging an issue 
        /// </summary>
        /// <param name="ept"></param>
        /// <returns></returns>
        public string expression()
        {
            ExpressionParser parser = ExpressionParserFactory.GetParser();
            return parser.Parse(data, field, op);
        }

    }
    
    /// <summary>
    /// A class made to hold all the information that gets posted by the jqgrid
    /// </summary>
    public class GridSettings
    {
        public GridSettings(string isSearch, string pageIndex, string pageSize, string sortColumn,
            string sortOrder, string filter)
        {
            IsSearch = bool.Parse(isSearch ?? "false");
            PageIndex = int.Parse(pageIndex ?? "1");
            PageSize = int.Parse(pageSize ?? "20");
            SortColumn = sortColumn.Split(new char[] { ',' }) ?? new string[] { };
            SortOrder = sortOrder.Split(new char[] { ',' }) ?? new string[] { }; ;
            Filter = Filter.Create(filter ?? "");

        }

        public bool IsSearch { get; set; }
        public int PageSize { get; set; }
        public int PageIndex { get; set; }
        public string[] SortColumn { get; set; }
        public string[] SortOrder { get; set; }
        public Filter Filter { get; set; }

        public string GetOrderByExpression()
        {
            if (SortColumn.Count() > 0)
            {
                string orderByExpression = "";

                for (int i = 0; i < SortColumn.Count(); i++)
                {
                    if (SortColumn.Length == SortOrder.Length)
                        orderByExpression += SortColumn[i] + " " + (SortOrder[i].ToUpper() ?? "ASC") + ", ";
                    else
                        orderByExpression += SortColumn[i] + " " + (SortOrder[0].ToUpper() ?? "ASC") + ", ";
                }

                return orderByExpression.Substring(0, orderByExpression.Length - 2);

            }
            else
            {
                return "";
            }
        }

        public string GetFilterExpression(ExpressionParserFactory.ExpressionParserType ept)
        {
            if (Filter != null)
            {
                string whereExpression = "";
                foreach (JqGrid.Rule r in Filter.rules)
                {
                    whereExpression += r.expression(ept) + " and ";
                }

                //if there are no rules in the filter set then it will crash
                try
                {
                    return whereExpression.Substring(0, whereExpression.Length - 5);
                }
                catch
                {
                    return "";
                }


                
            }
            else
            {
                return "";
            }
        }



        /// <summary>
        /// Returns a generic filter expression not suitable for use on any dataset but may be useful for debugging purposes
        /// </summary>
        /// <returns></returns>
        public string GetFilterExpression()
        {
            if (Filter != null)
            {
                string whereExpression = "";
                foreach (JqGrid.Rule r in Filter.rules)
                {
                    whereExpression += r.expression() + " and ";
                }
                try
                {
                    return whereExpression.Substring(0, whereExpression.Length - 5);
                }
                catch
                {
                    return "";
                }
            }
            else
            {
                return "";
            }
        }



    }

    /// <summary>
    /// Parser provider
    /// </summary>
    public static class ExpressionParserFactory
    {
        //depending on how you want to implement your searching there will be slight differences in the 
        public enum ExpressionParserType { Oracle, MicrosoftDataSet, MicrosoftSQLServer, MySQL, PostgreSQL }
        
        public static ExpressionParser GetParser(ExpressionParserType ept)
        {
            switch(ept)
            {
                case ExpressionParserType.Oracle:
                    return new OracleExpressionParser();                
                case ExpressionParserType.MySQL:
                    return new MySQLExpressionParser();
                case ExpressionParserType.PostgreSQL:
                    return new PostgreSQLExpressionParser();
                case ExpressionParserType.MicrosoftSQLServer:
                    return new MicrosoftSQLServerExpressionParser();
                default:
                    return new MicrosoftDataSetExpressionParser();
            }
        }

        public static ExpressionParser GetParser()
        {
            
            return new GenericExpressionParser();
            
        }



    }

    /// <summary>
    /// Base class has implementations for parsing number and boolean datatypes since they seem to be uniform.  
    /// You are more than welcome to override them if you feel you need something different for your dataset 
    /// </summary>
    public abstract class ExpressionParser
    {
        public string Parse(string data, string field, string op)
        {
            JqgridHelper.DataType dt = JqgridHelper.GetDataType(data);
            switch (dt)
            {
                case JqgridHelper.DataType.String:
                    return ParseString(data, field, op);
                case JqgridHelper.DataType.Date:
                    return ParseDate(data, field, op);
                case JqgridHelper.DataType.Number:
                    return ParseNumber(data, field, op);
                default:  //this is for boolean
                    return ParseBoolean(data, field, op);
            }
        }

        protected string ParseNumber(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return field + " = " + data;
                case "ne":
                    return field + " <> " + data;
                case "lt":
                    return field + " < " + data;
                case "le":
                    return field + " <= " + data;
                case "gt":
                    return field + " > " + data;
                case "ge":
                    return field + " >= " + data;
                case "bw":
                    return field + " = " + data;
                case "bn":
                    return field + " <> " + data;
                case "ew":
                    return field + " = " + data;
                case "en":
                    return field + " <> " + data;
                case "cn":
                    return field + " = " + data;
                case "nc":
                    return field + " <> " + data;
                default:
                    return "";
            }


        }

        protected string ParseBoolean(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return field + " = " + data;
                case "ne":
                    return field + " <> " + data;
                case "lt":
                    return field + " <> " + data;
                case "le":
                    return field + " <> " + data;
                case "gt":
                    return field + " <> " + data;
                case "ge":
                    return field + " <> " + data;
                case "bw":
                    return field + " = " + data;
                case "bn":
                    return field + " <> " + data;
                case "ew":
                    return field + " = " + data;
                case "en":
                    return field + " <> " + data;
                case "cn":
                    return field + " = " + data;
                case "nc":
                    return field + " <> " + data;
                default:
                    return "";
            }

        }

        protected abstract string ParseString(string data, string field, string op);

        protected abstract string ParseDate(string data, string field, string op);

    }

    /// <summary>
    /// Various parsers for use with different types of data collections
    /// </summary>
    public class OracleExpressionParser : ExpressionParser
    {

        protected override string ParseString(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return "UPPER(" + field + ") = '" + data.ToUpper() + "'";
                case "ne":
                    return "UPPER(" + field + ") <> '" + data.ToUpper() + "'";
                case "lt":
                    return "UPPER(" + field + ") < '" + data.ToUpper() + "'";
                case "le":
                    return "UPPER(" + field + ") <= '" + data.ToUpper() + "'";
                case "gt":
                    return "UPPER(" + field + ") > '" + data.ToUpper() + "'";
                case "ge":
                    return "UPPER(" + field + ") >= '" + data.ToUpper() + "'";
                case "bw":
                    return "UPPER(" + field + ") like '" + data.ToUpper() + "%'";
                case "bn":
                    return "UPPER(" + field + ") not like '" + data.ToUpper() + "%'";
                case "ew":
                    return "UPPER(" + field + ") like '%" + data.ToUpper() + "'";
                case "en":
                    return "UPPER(" + field + ") not like '%" + data.ToUpper() + "'";
                case "cn":
                    return "UPPER(" + field + ") like '%" + data.ToUpper() + "%'";
                case "nc":
                    return "UPPER(" + field + ") not like '%" + data.ToUpper() + "%'";
                default:
                    return "";
            }
        }

        protected override string ParseDate(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return "TRUNC(" + field + ") = '" + data + "'";
                case "ne":
                    return "TRUNC(" + field + ") <> '" + data + "'";
                case "lt":
                    return "TRUNC(" + field + ") < '" + data + "'";
                case "le":
                    return "TRUNC(" + field + ") <= '" + data + "'";
                case "gt":
                    return "TRUNC(" + field + ") > '" + data + "'";
                case "ge":
                    return "TRUNC(" + field + ") >= '" + data + "'";
                default:
                    throw new NotImplementedException();
            }
        }

    }

    public abstract class MicrosoftExpressionParser : ExpressionParser
    {
        protected override string ParseString(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return field + " = '" + data + "'";
                case "ne":
                    return field + " <> '" + data + "'";
                case "lt":
                    return field + " < '" + data + "'";
                case "le":
                    return field + " <= '" + data + "'";
                case "gt":
                    return field + " > '" + data + "'";
                case "ge":
                    return field + " >= '" + data + "'";
                case "bw":
                    return field + " like '" + data + "%'";
                case "bn":
                    return field + " not like '" + data + "%'";
                case "ew":
                    return field + " like '%" + data + "'";
                case "en":
                    return field + " not like '%" + data + "'";
                case "cn":
                    return field + " like '%" + data + "%'";
                case "nc":
                    return field + " not like '%" + data + "%'";
                default:
                    return "";
            }
        }

    }

    public class MicrosoftDataSetExpressionParser : MicrosoftExpressionParser
    {

        /// <summary>
        /// This is supposed to compare a date coming in from the jqgrid and compare it to a date and possibly a datetime object in your datatable
        /// since a select method you would pass this on to is somewhat limited i've had to think of ways of getting around that fact that i can't 
        /// truncate, cast, or convert the field into only a date.  If someone knows a way I really would appreciate it!!!
        /// </summary>
        /// <param name="data"></param>
        /// <param name="field"></param>
        /// <param name="op"></param>
        /// <returns></returns>
        protected override string ParseDate(string data, string field, string op)
        {
            data = DateTime.Parse(data).ToString("MM/dd/yyyy");
            switch (op)
            {
                case "eq":
                    return field + " > '" + DateTime.Parse(data) + "' AND " + field + " < '" + DateTime.Parse(data).AddDays(1) + "'";
                case "ne":
                    return field + " < '" + DateTime.Parse(data) + "' AND " + field + " >= '" + DateTime.Parse(data).AddDays(1) + "'"; 
                case "lt":
                    return field + " <= '" + DateTime.Parse(data).AddDays(1) + "'" ;
                case "le":
                    return field + " < '" + DateTime.Parse(data).AddDays(1) + "'";
                case "gt":
                    return field + " >= '" + DateTime.Parse(data).AddDays(1) + "'";
                case "ge":
                    return field + " >= '" + DateTime.Parse(data) + "'";
                default:
                    throw new NotImplementedException("eq, ne, lt, gt and ge are the only operators supported for dates");
            }
        }

    }

    public class MicrosoftSQLServerExpressionParser : MicrosoftExpressionParser
    {

        protected override string ParseDate(string data, string field, string op)
        {
            data = DateTime.Parse(data).ToString("MM/dd/yyyy");
            switch (op)
            {
                case "eq":
                    return "CAST(" + field + " As Date) = '" + data + "'";
                case "ne":
                    return "CAST(" + field + " As Date) <> '" + data + "'";
                case "lt":
                    return "CAST(" + field + " As Date) < '" + data + "'";
                case "le":
                    return "CAST(" + field + " As Date) <= '" + data + "'";
                case "gt":
                    return "CAST(" + field + " As Date) > '" + data + "'";
                case "ge":
                    return "CAST(" + field + " As Date) >= '" + data + "'";
                default:
                    throw new NotImplementedException("eq, ne, lt, gt and ge are the only operators supported for dates");
            }
        }

    }


    public class GenericExpressionParser : ExpressionParser
    {
        private string Parse(string data, string field, string op)
        {
            switch (op)
            {
                case "eq":
                    return field + " = " + data;
                case "ne":
                    return field + " <> " + data;
                case "lt":
                    return field + " < " + data;
                case "le":
                    return field + " <= " + data;
                case "gt":
                    return field + " > " + data;
                case "ge":
                    return field + " >= " + data;
                case "bw":
                    return field + " like " + data;
                case "bn":
                    return field + " not like " + data;
                case "ew":
                    return field + " like " + data;
                case "en":
                    return field + " not like " + data;
                case "cn":
                    return field + " like " + data;
                case "nc":
                    return field + " not like " + data;
                default:
                    return "";
            }
        }

        protected override string ParseDate(string data, string field, string op)
        {
            return Parse(data, field, op);
        }

        protected override string ParseString(string data, string field, string op)
        {
            return Parse(data, field, op);
        }


    }


    public class MySQLExpressionParser : ExpressionParser
    {
        protected override string ParseDate(string data, string field, string op)
        {
            throw new NotImplementedException();
        }

        protected override string ParseString(string data, string field, string op)
        {
            throw new NotImplementedException();
        }
    }

    public class PostgreSQLExpressionParser : ExpressionParser
    {
        protected override string ParseDate(string data, string field, string op)
        {
            throw new NotImplementedException();
        }

        protected override string ParseString(string data, string field, string op)
        {
            throw new NotImplementedException();
        }

    }

}


namespace JqGrid.XML
{

    public static class JqgridHelper
    {
        /// <summary>
        /// This will accept a datatable that has already been filtered and ordered and a GridSettings object and will return the appropriate
        /// XML Object to return to the jqgrid.  If page size is 0 then all rows will be returned
        /// </summary>
        /// <param name="dt">System.Data.DataTable filtered and sorted datetable</param>
        /// <param name="gs">JqGrid.GridSettings</param>
        /// <returns></returns>
        public static JQGrid GetJQGrid(DataTable dt, GridSettings gs)
        {

            JQGrid jqGrid = new JQGrid();
            int startIndex = 0;
            int endIndex = dt.Rows.Count;

            //possibly useful for debugging
            //jqGrid.filterExpression = gs.GetFilterExpression();
            //jqGrid.orderExpression = gs.GetOrderByExpression();
            //jqGrid.search = gs.IsSearch;

            //try{ jqGrid.numberRules = gs.Filter.rules.Count(); }
            //catch {  }


            if (gs.PageIndex > 0)
            {
                startIndex = (gs.PageIndex - 1) * gs.PageSize;
                endIndex = (gs.PageIndex * gs.PageSize) <= dt.Rows.Count ? (gs.PageIndex * gs.PageSize) : dt.Rows.Count;
            }

            for (int i = startIndex; i < endIndex; i++)
            {
                row row = new row();
                row.id = i;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.cells.Add(dt.Rows[i][j].ToString());
                }
                jqGrid.rows.Add(row);
            }

            jqGrid.page = gs.PageIndex;  //the current page you are looking at
            jqGrid.records = dt.Rows.Count; //the current page you are looking at
            jqGrid.total = (int)Math.Ceiling((float)dt.Rows.Count / (float)gs.PageSize);  // Set this to total pages in your result...

            return jqGrid;
        }
    }

    public class row
    {

        public int id { get; set; }

        [System.Xml.Serialization.XmlElement("cell")]
        public List<string> cells { get; set; }

        public row()
        {
            cells = new List<string>();
        }
    }

    [System.Xml.Serialization.XmlRoot("rows")]
    public class JQGrid
    {

        //I was using these parameters for dubugging an issue you can uncomment them if you need to 
        //public string parameters { get; set; }
        //public bool search { get; set; }
        //public string filterExpression { get; set; }
        //public string orderExpression { get; set; }
        //public int numberRules { get; set; }


        public int page { get; set; }
        public int total { get; set; }
        public int records { get; set; }
        

        public List<row> rows { get; set; }

        public JQGrid()
        {
            rows = new List<row>();
        }
    }
}


namespace JqGrid.JSON
{

    public static class JqgridHelper
    {
        /// <summary>
        /// This will accept a datatable that has already been filtered and ordered and a GridSettings object and will return the appropriate
        /// XML Object to return to the jqgrid
        /// </summary>
        /// <param name="dt">System.Data.DataTable filtered and sorted datetable</param>
        /// <param name="gs">JqGrid.GridSettings</param>
        /// <returns></returns>
        public static JQGrid GetJQGrid(DataTable dt, GridSettings gs)
        {

            JQGrid jqGrid = new JQGrid();

            int startIndex = 0;
            int endIndex = dt.Rows.Count;

            //jqGrid.filterExpression = gs.GetFilterExpression();
            //jqGrid.orderExpression = gs.GetOrderByExpression();
            //jqGrid.search = gs.IsSearch;

            //try { jqGrid.numberRules = gs.Filter.rules.Count(); }
            //catch { }




            if (gs.PageIndex > 0)
            {
                startIndex = (gs.PageIndex - 1) * gs.PageSize;
                endIndex = (gs.PageIndex * gs.PageSize) <= dt.Rows.Count ? (gs.PageIndex * gs.PageSize) : dt.Rows.Count;
            }

            for (int i = startIndex; i < endIndex; i++)
            {
                row row = new row();
                row.id = i;
                for (int j = 0; j < dt.Columns.Count; j++)
                {

                    string value = dt.Rows[i][j].ToString();
                    string columnName = dt.Columns[j].ColumnName.ToUpper();

                    if (JqGrid.JqgridHelper.GetDataType(value) == JqGrid.JqgridHelper.DataType.Date)
                        value = DateTime.Parse(value).ToString("dd-MMM-yyyy");

                    row.cells.Add(value);
                }
                jqGrid.rows.Add(row);
            }

            jqGrid.page = gs.PageIndex;
            jqGrid.records = dt.Rows.Count;
            jqGrid.total = (int)Math.Ceiling((float)dt.Rows.Count / (float)gs.PageSize);  // Set this to total pages in your result...

            return jqGrid;
        }
    }

    public class row
    {

        public int id { get; set; }

        public List<string> cells { get; set; }

        public row()
        {
            cells = new List<string>();
        }
    }

    public class JQGrid
    {

        //I was using these parameters for dubugging an issue you can uncomment them if you need to 
        //public string parameters { get; set; }
        //public int numberRules { get; set; }
        //public bool search { get; set; }
        //public string filterExpression { get; set; }
        //public string orderExpression { get; set; }

        public int page { get; set; }
        public int total { get; set; }
        public int records { get; set; }


        public List<row> rows { get; set; }

        public JQGrid()
        {
            rows = new List<row>();
        }
    }

}

